﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MODEL;
using System.Data.OleDb;
using System.Data;
namespace DAL
{
    public class DalStock
    {
        public int Insert(Stock s)
        {
            string sql = "insert into stock(number,dicid,goodname,classid) values(@number,@dicid,@goodname,@classid)";
            OleDbParameter[] pms = new OleDbParameter[]
                {
                    new OleDbParameter("@number",s.Number),
                    new OleDbParameter("@dicid",s.DicId),
                    new OleDbParameter("@goodname",s.GoodsName),
                    new OleDbParameter("@classid",s.ClassId) 
                };
            return AccessHelp.ExecteNonQuery(sql,pms);
        }
        public int Update(Stock s)
        {
            string sql = "update stock set goodsnumber=@number,classid=@classid,goodsname=@goodsname,dicid=@dicid,goodspy=@goodspy where id=@id";
            OleDbParameter[] pms = new OleDbParameter[]
                {
                    new OleDbParameter("@number",s.Number),
                    new OleDbParameter("@dicid",s.DicId),
                    new OleDbParameter("@goodsname",s.GoodsName),
                    new OleDbParameter("@classid",s.ClassId),
                    new OleDbParameter("@goodspy",s.PY),
                    new OleDbParameter("@id",s.Id),
                    
                };
            return AccessHelp.ExecteNonQuery(sql,pms);
        }

        public int UpdateStockNum(int num, int id)
        {
            string sql = "update stock set goodsnumber=@num where id=@id";
            return AccessHelp.ExecteNonQuery(sql,new OleDbParameter("@num",num), new OleDbParameter("@id", id));
        }

        public int IsExistByDicId(int id)
        {
            string sql = "select id from stock where dicid=@dicid";
            return Convert.ToInt32(AccessHelp.ExecuteScalar(sql,new OleDbParameter("dicid", id)));
        }

        public List<Stock> GetByPY(string namePY)
        {
            //string sql = "select s.id,s.goodsnumber,d.goodsname,d.goodsmodel,d.goodsspe,d.goodsunit,d.goodprice,d.PY,s.dicid,d.classid from stock s left join dic d on s.dicid = d.id where d.PY like @namepy and d.del=0";
            string sql = "select id,goodsnumber,dicid,goodsname,classid,goodspy from stock where goodspy like @namepy";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@namepy", "%"+namePY+"%"));
            //DataTable dt = AccessHelp.ExecuteReader(sql, null);
            return TableToStock(dt);
        }
        public List<Stock> TableToStock(DataTable dt)
        {
            List<Stock> stocks = new List<Stock>();
            foreach (DataRow row in dt.Rows)
            {
                Stock s = new Stock();
                s.Id = (int)row[0];
                s.Number = (int)row[1];
                s.DicId = (int)row[2];
                s.GoodsName = row[3].ToString();
                s.ClassId = (int)row[4];
                s.PY = row[5].ToString();
                //s.Dic.GoodsName = row[2].ToString();
                //s.Dic.GoodsModel = row[3].ToString();
                //s.Dic.GoodsSpe = row[4].ToString();
                //s.Dic.GoodsUnit = row[5].ToString();
                //s.Dic.GoodsPrice = (Double)row[6];
                //s.Dic.Pingying = row[7].ToString();
                stocks.Add(s);
            }
            return stocks;
        }

        public Stock GetById(int id)
        {
            string sql = "select id,goodsnumber,dicid,goodsname,classid,goodspy from stock where id=@id";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@id", id));
            return TableToStock(dt).FirstOrDefault();
        }

        public List<Stock> GetAll()
        {
            string sql = "select id,goodsnumber,dicid,goodsname,classid,goodspy from stock";
            DataTable dt = AccessHelp.ExecuteReader(sql,null);
            return TableToStock(dt);
        }
    }
}
